﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class RC_PlacementBySelectBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);




        public void insert(int cmd_seq,int tmp_emp_seq,int move_seq,int tmp_edu_seq,double salary,
    string pos_name ,string pos_ref_no,int class_seq,int job_site_seq ,string job_site_name
, string start_date, int pos_type_seq,string remarks, int created_by)
        {
            try
                          
            {
                DateTime startDate;

                startDate  = Convert.ToDateTime(start_date);  

           //     startDate = Convert.ToDateTime(Utils.Date.ToEngFormatDate(start_date));     
                
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@salary",salary);
                dbManager.AddParameters(5, "@pos_name", pos_name);
                dbManager.AddParameters(6, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(7, "@class_seq", class_seq);
                dbManager.AddParameters(8, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(9, "@job_site_name", job_site_name);
                dbManager.AddParameters(10, "@start_date", startDate);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus  );
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Today);
                dbManager.AddParameters(15, "@pos_type_seq", pos_type_seq);
           
                  string sql;

                  sql = " insert into hrm_ps_command_add(cmd_seq,tmp_emp_seq,move_seq,tmp_edu_seq,salary,"
      + " pos_name ,pos_ref_no,class_seq,job_site_seq ,job_site_name,"
      + " start_date,remarks,record_status,pos_type_seq,created_by,created_when)"


      + " values(@cmd_seq,@tmp_emp_seq,@move_seq,@tmp_edu_seq,@salary,"
      + " @pos_name ,@pos_ref_no,@class_seq,@job_site_seq ,@job_site_name,"
      + " @start_date,@remarks,@record_status,@pos_type_seq,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }





        public void Update(int cmd_add_seq,int move_seq, int tmp_edu_seq,double salary,
string pos_name, string pos_ref_no, int class_seq, int job_site_seq, string job_site_name
, string start_date, int pos_type_seq, string remarks, int updated_by)
        {
            try
            {
                DateTime startDate;

                startDate = Convert.ToDateTime(start_date);

             

                dbManager.Open();
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@salary", salary);
                dbManager.AddParameters(5, "@pos_name", pos_name);
                dbManager.AddParameters(6, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(7, "@class_seq", class_seq);
                dbManager.AddParameters(8, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(9, "@job_site_name", job_site_name);
                dbManager.AddParameters(10, "@start_date", startDate);
                dbManager.AddParameters(11, "@remarks", remarks);
                dbManager.AddParameters(12, "@updated_by", updated_by);
                dbManager.AddParameters(13, "@updated_when", DateTime.Today);
              
                

                string sql;

                sql = " update  hrm_ps_command_add set"
                + " move_seq=@move_seq,"
                + " tmp_edu_seq=@tmp_edu_seq,"
                + " salary=@salary,"
                + " pos_name=@pos_name,"
                + " pos_ref_no=@pos_ref_no,"
                + " class_seq=@class_seq,"
                + " job_site_seq=@job_site_seq,"
                + " job_site_name=@job_site_name,"
                + " start_date=@start_date,"
                + " remarks=@remarks,"
                + " pos_type_seq=@pos_type_seq,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where cmd_add_seq=@cmd_add_seq";



                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }





        public DataTable Select(int cmd_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "SELECT"
+ " concat(prefix_name,'  ',first_name,'    ',last_name) as full_name,"
+ " cmd_add.job_site_name,"
+ " cmd_add.salary,"
+ " cmd_add.pos_name,"
+ " class.class_name,"
+ " cmd_add.remarks,"
+ " prefix.prefix_name,"
+ " cmd_add.cmd_add_seq,"
+ " cmd_add.tmp_edu_seq,"
+ " cmd_add.move_seq,"
+ " cmd_add.old_salary,"
+ " cmd_add.old_job_site_name,"
+ " cmd_add.old_pos_type_seq,"
+ " cmd_add.pos_ref_no,"
+ " cmd_add.pos_seq,"
+ " cmd_add.class_seq,"
+ " cmd_add.old_pos_name,"
+ " cmd_add.old_class_seq,"
+ " cmd_add.start_date,"
+ " cmd_add.pos_type_seq,"
+ " cmd_add.job_site_seq,"
+ " cmd_add.tmp_emp_seq"
+ " FROM"
+ " hrm_tmp_employees AS tmp_emp"
+ " Inner Join hrm_ps_command_add AS cmd_add ON cmd_add.tmp_emp_seq = tmp_emp.tmp_emp_seq"
+ " Inner Join hrm_mp_classes AS class ON cmd_add.class_seq = class.class_seq"
+ " Inner Join mas_name_prefixes AS prefix ON tmp_emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where cmd_add.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and cmd_add.cmd_seq = " + cmd_seq ;
                   

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public Boolean chkDup(int cmd_seq,int tmp_emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_ps_command_add"
                    + " where cmd_seq = " + cmd_seq
                    + " and tmp_emp_seq = " + tmp_emp_seq
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet(); 
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


               
            }

            catch (Exception ex)
            
            {
                throw(new Exception(ex.Message)) ;  
            }

            finally
            {
                dbManager.Dispose();   
            }


        }


        public DataTable SelectTmpEmp()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
+ " emp.tmp_emp_seq "
+ " FROM"
+ " hrm_tmp_employees AS emp"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where emp.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectTmpEmpByID(int id)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
+ " emp.tmp_emp_seq, "
+ " emp.citizen,"
+ " emp.birth_date"
+ " FROM"
+ " hrm_tmp_employees AS emp"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where emp.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and tmp_emp_seq=" + id;





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from mas_record_statuses  "
                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }


 
 
        public void Delete(int cmd_add_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
               

                sql = " update hrm_ps_command_add set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where cmd_add_seq = @cmd_add_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }









        public DataTable SelectDialogOldPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogEntPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select *"
                    + " from hrm_mp_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }


        public DataTable SelectComboTmpEdu(int tmp_emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select "
+ " qua.edu_qual_name,"
+ " qua.edu_qual_seq,"
+ " emp_edu.tmp_emp_seq"
+ " FROM"
+ " mas_edu_qualifications AS qua"
+ " Inner Join hrm_tmp_employee_educations AS emp_edu ON qua.edu_qual_seq = emp_edu.edu_qual_seq"
+ " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_edu.tmp_emp_seq=" + tmp_emp_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }



        public DataTable BindTxtMajor(int edu_qual_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " emp_edu.edu_qual_seq,"
+ " major.edu_major_name,"
+ " ins.edu_ins_name"
+ " FROM"
+ " mas_edu_majors AS major"
+ " Inner Join hrm_tmp_employee_educations AS emp_edu ON major.edu_major_seq = emp_edu.edu_major_seq"
+ " Inner Join mas_edu_institutes AS ins ON emp_edu.edu_ins_seq = ins.edu_ins_seq"

+ " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_edu.edu_qual_seq = " + edu_qual_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }




        public DataTable BindPosition(int pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " pos.pos_seq,"
+ " pos.pos_ref,"
+ " pos.pos_name,"
+ " pos.job_site_seq,"
+ " field_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_positions AS pos"
+ " Inner Join hrm_mp_field_positions AS field_pos ON pos.field_pos_seq = field_pos.field_pos_seq"

+ " where pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and pos.pos_seq = " + pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }


        public DataTable BindPositionType(int field_pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " field_pos.field_pos_seq,"
+ " field_pos.field_pos_line,"
+ " type_pos.pos_type_name,"
+ " type_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_field_positions AS field_pos"
+ " Inner Join hrm_mp_position_types AS type_pos ON field_pos.pos_type_seq = type_pos.pos_type_seq"

+ " where field_pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and field_pos.field_pos_seq = " + field_pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }





    







     

   
    }
}
